import logging
class dbi:
    def __init__(self, cur):
        self.log = logging.getLogger(self.__class__.__name__)
        self.cur = cur
        self.dt_dict = { 'text' : 'text',
                         'primary_key' : 'integer primary key ASC',
                         'integer': 'integer',
                         'real': 'real',
                         'blob': 'blob',
                       }

    def create_table(self, tb_name, tb_def, failonexists=False):
        '''
            tb_name: name of the table
            tb_def : table definition, a list of two element tuple
            accepted data type list:
                primary_key, integer, real, text, blob
        '''
        sql = 'create table '
        if failonexists == False:
            sql = sql + 'if not exists %s' % tb_name
        else:
            sql = sql + tb_name

        sql = sql+' (' # start table def
        for item in tb_def:
            sql = sql + item[0] + ' ' + self.dt_dict[item[1]] + ','

        sql = sql[:-1] # strip the last ','
        sql = sql+');' # end of statement

        self.log.debug('create_table: %s', sql)
        self.cur.execute(sql)

    def create_index(self, index_name, tb_name, cols, unique=False, failonexists=False):
        sql = 'create '
        if unique == True:
            sql = sql + 'unique '
        sql = sql + 'index '
        if failonexists == False:
            sql = sql + 'if not exists '
        sql = sql + index_name + ' on ' + tb_name + '('
        for col in cols:
            sql = sql + col + ','
        sql = sql[:-1] # strip the last comma
        sql = sql + ')'

        self.log.debug('create_index: %s', sql)
        self.cur.execute(sql)

    def insert(self, tb_name, vd, replace=False):
        sql = 'insert '
        if replace == True:
            sql = sql + 'or replace '
        sql = sql + 'into ' + tb_name
        sql = sql + ' ('
        keys = vd.keys()
        for k in keys:
            sql = sql + k + ','
        sql = sql[:-1] + ') values ('

        for k in keys:
            sql = sql + '?,'
        sql = sql[:-1] + ')'

        t = [vd[k] for k in keys]
        self.log.debug('insert: %s, %s' % (sql, str(t)))
        self.cur.execute(sql, t)
